drop table jms_dm.dm_tab_scan_piece_day_summary_dt;
CREATE TABLE jms_dm.dm_tab_scan_piece_day_summary_dt
(   scan_type_code    varchar(50) comment '扫描类型code 1：发件 2：到件 3：拆包',
    network_code      varchar(50) comment '网点code',
    scan_user_code    varchar(50) comment '扫描员code',
    organization_code varchar(50) comment '中心组织code',
    supplier_id       varchar(50) comment '供应商id',
    organization_name varchar(200) comment '中心组织name',
    supplier_code     varchar(50) comment '供应商编码',
    supplier_name     varchar(300) comment '供应商名',
    scan_type         varchar(50) comment '扫描类型',
    network_name      varchar(200) comment '网点名',
    network_type      tinyint comment '网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
    pre_site_code     varchar(50) comment '上一站code',
    pre_site_name     varchar(200) comment '上一站',
    next_site_code    varchar(50) comment '下一站code',
    next_site_name    varchar(200) comment '下一站',
    shipment_no       varchar(50) comment '任务号',
    shipment_name     varchar(300) comment '任务名称',
    scan_user         varchar(50) comment '扫描员',
    scan_user_id      varchar(50) comment '扫描员id',
    scan_user_type    varchar(200) comment '扫描员员工属性1临时工/2正式工/3小时工(已废弃)/4职能/5环节外包',
    parts_cnt         bigint comment '散件件数',
    packages_cnt      bigint comment '包件数',
    in_packages_cnt   bigint comment '包内件数',
    scan_cnt          bigint comment '扫描件数',
    weight            decimal(10, 2) comment '重量',
    table_type tinyint COMMENT '任务类型；1,干线；2,支线',
    bz_type tinyint COMMENT '类型；1,出港；2,进港',
    one_id varchar(300) COMMENT '唯一id',
    date_time         date comment 'sr分区'
) ENGINE = OLAP DUPLICATE KEY( `scan_type_code`,`network_code`, `scan_user_code`, `organization_code`,`supplier_id` )
COMMENT '计件统计'
PARTITION BY RANGE(`date_time`)
(START ('2023-11-20') END ('2023-12-10') EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`network_code`) BUCKETS 2
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-190",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "2",
"in_memory" = "false",
"storage_format" = "V2"
);